﻿using API_lib.Exceptions;
using API_lib.Persistence.Model;
using API_lib.Persistence.SQL;
using API_lib.Util;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Reflection;
using System.Text;

namespace API_lib.Persistence.DAO
{
    /// <summary>
    /// The base (abstract) class of all DAO objects implementing all commonly used methods and
    /// defines the ones to be implemented in all the subclasses.
    ///
    /// Param <BaseMO> to handle by the created instance.
    /// </summary>
    public abstract class AbstractDAO : PureDAO, DataAccessInterface<BaseMO>
    {
        // ABSTRACT METHODS

        /// <summary>
        /// Returns a value object from a current record from a SQLiteDataReader object.
        /// </summary>
        /// <param name="recset">Object - the actual row will be transformed
	    /// to a value object. </param>
        /// <returns>A value object from the current record.</returns>
	    protected abstract BaseMO getRowAsMO(SQLiteDataReader recset);

        /// <summary>
        /// Returns the name of the database table this DAO uses.
        /// </summary>
        /// <returns>The name of the database table.</returns>
        protected abstract String getDatabaseTableName();

        /// <summary>
        /// Returns the String representation of the table field names.
        /// </summary>
        /// <returns>The fields of the table.</returns>
        protected abstract List<String> getDatabaseTableFieldNames();

        /// <summary>
        /// Returns the String representation of the table field SQL types as string eg.: VARCHAR, INTEGER...
        /// </summary>
        /// <returns>The fields of the table.</returns>
        protected abstract List<String> getDatabaseTableFieldTypes();

        // METHODS FROM DataAccessInterface

        public void insert(BaseMO obj)
        {
            validate(obj);

            List<String> fields =  new List<String>();
            List<String> values =  new List<String>();

            PropertyInfo[] properties = obj.GetType().GetProperties();

            foreach(PropertyInfo i in properties)
            {
                if (i.Name != "id")
                {
                    fields.Add(i.Name);
                    Object value = i.GetValue(obj, null);
                    String str = null;

                    if (i.PropertyType == Type.GetType("System.String"))
                    {
                        str = SQLQueries.getFieldValueQuoted(value.ToString());
                    }
                    else if (i.PropertyType == Type.GetType("System.DateTime"))
                    {
                        str = SQLQueries.getFieldValueQuoted(((DateTime)value).ToString(SQLConstants.FORMATTER_DATE));
                    }
                    else if(i.PropertyType == Type.GetType("System.Boolean"))
                    {
                        str = Convert.ToInt16((Boolean)i.GetValue(obj, null)).ToString();
                    }
                    else
                    {
                        str = value.ToString();
                        str = str.Replace(',', '.');
                    }

                    values.Add(str);
                }
            }

            executeSqlSQLStatement(SQLQueries.createInsertQuery(getDatabaseTableName(), fields, values));
        }

        public int insertReturnPK(BaseMO obj)
        {
            insert(obj);
            
            String[] fields = new[] { "MAX(id) as newid" };
            int result = getIntegerSqlResult(SQLQueries.createSelectQuery(getDatabaseTableName(), fields, "", ""), "newid");

            return result;
        }

        public void update(BaseMO obj)
        {
             validate(obj);

            List<String> fields =  new List<String>();
            List<String> values =  new List<String>();

            PropertyInfo[] properties = obj.GetType().GetProperties();

            foreach (PropertyInfo i in properties)
            {
                if (i.Name != "id")
                {
                    fields.Add(i.Name);
                    Object value = i.GetValue(obj, null);
                    String str = null;

                    if (i.PropertyType == Type.GetType("System.String"))
                    {
                        str = SQLQueries.getFieldValueQuoted(value.ToString());
                    }
                    else if (i.PropertyType == Type.GetType("System.DateTime"))
                    {
                        str = SQLQueries.getFieldValueQuoted(((DateTime)value).ToString(SQLConstants.FORMATTER_DATE));
                    }
                    else if (i.PropertyType == Type.GetType("System.Boolean"))
                    {
                        str = Convert.ToInt16((Boolean)i.GetValue(obj, null)).ToString();
                    }
                    else
                    {
                        str = value.ToString();
                    }

                    values.Add(str);
                }
            }

            executeSqlSQLStatement(SQLQueries.createUpdateQuery(getDatabaseTableName(), fields, values, createWhereClauseByPK(obj)));
        }

        public void delete(BaseMO obj)
        {
            validate(obj);

            executeSqlSQLStatement(SQLQueries.createDeleteQuery(getDatabaseTableName(), createWhereClauseByPK(obj)));
        }

        public bool isExists(BaseMO obj)
        {
            return find(obj.id).HasRows;
        }

        public SQLiteDataReader find(int id)
        {
            StringBuilder result = new StringBuilder(50);

            result.Append("id").Append(SQLConstants.SYMBOL_SPACE + SQLConstants.SYMBOL_EQUALS + SQLConstants.SYMBOL_SPACE + id);
            String statement = result.ToString();

            return getSQLiteDataReaderResult(SQLQueries.createSelectQueryAllFields(getDatabaseTableName(), statement, ""));
            
        }

        public SQLiteDataReader getAll()
        {
            return getSQLiteDataReaderResult(SQLQueries.createSelectQueryAllFields(getDatabaseTableName(), "", ""));
        }

        public bool validate(BaseMO obj)
        {
            Boolean valid = false;
            if (obj != null)
            {
                valid = true;
            }
            else
            {
                DAOException ex = new DAOException("Model object invalid.");
                ErrorLogger.logError(ex);
                throw ex;
            }

            return valid;
        }

        public void createTable()
        {
            String stm = SQLQueries.createCreateQuery(getDatabaseTableName(), getDatabaseTableFieldsDescription());

            executeSqlSQLStatement(stm);
        }

        // PRIVATE METHODS

        /// <summary>
        /// Joins the fieldnames list with the field types list, separated by commas.
        /// </summary>
        /// <returns>The fields description string for the table.</returns>
        private String getDatabaseTableFieldsDescription()
        {
            StringBuilder result = new StringBuilder(1000);

            StringBuilder separator = new StringBuilder(2).Append(SQLConstants.SYMBOL_SEPARATOR_FIELD + SQLConstants.SYMBOL_SPACE);

            List<String> fields =  getDatabaseTableFieldNames();
            List<String> types = getDatabaseTableFieldTypes();

            if (fields.Count == types.Count)
            {
                for (int i = 0; i < fields.Count; i++)
			    {
			        result.Append(fields[i] + SQLConstants.SYMBOL_SPACE + types[i]);
                    result.Append(separator);
			    }
                // remove the last unneccessary ", "
                result = result.Remove(result.Length - 2, 2);
            }
            else
            {
                DAOException exc = new DAOException("Table fields and their types have different Count.");
                ErrorLogger.logError(exc);

                throw exc;
            }
            return result.ToString();
        }

        /// <summary>
        /// Creates a where clause string for the PK, it must be lowercase id 
        /// </summary>
        /// <param name="obj">The object whose id will be used.</param>
        /// <returns>The whereclause without the WHERE keyword, it will be added one level lower in the SQLQueries class.</returns>
        private String createWhereClauseByPK(BaseMO obj)
        {
            StringBuilder result = new StringBuilder(50);

            int id = Convert.ToInt32(obj.GetType().GetProperty("id").GetValue(obj, null));
            result.Append("id").Append(SQLConstants.SYMBOL_SPACE + SQLConstants.SYMBOL_EQUALS + SQLConstants.SYMBOL_SPACE + id);

            Console.WriteLine(result.ToString());

            return result.ToString();
        }

    }
}
